At times, database tables need to be reorganized or moved from one physical location to another physical location. For example, database tables may need to be reorganized or moved from one location to another to (a) change the table storage attributes, (b) change the data types, (c) drop or add columns, (d) reclaim space used by the table, or (e) relocate objects.
The accessibility of the database table during a reorganization or move may be limited. For database tables that are frequently used or used by many users it can be challenging if not impossible, to schedule a time to reorganize or move the database tables.
Some database vendors include procedures or functions for reorganizing or moving a database table. For example, the procedures or functions for reorganizing or moving a database table that some database vendors provide include a staging area for tracking the modifications that are made to a source or original table while the data of the source table is being reorganized or moved to a target or new table. After the data of the source table has been replicated in the target table, all of the modifications that were made to the source table during the move (and stored in the staging area) are replayed to the target table. Thus, there is a delay between the time that the data is changed in the source table and the replicating of the change in the target table.
In cases where changes are made to the data of the source table during the replay process, multiple iterations of the replay process are required. In some cases, for example, where the data in the source table is being modified frequently, the process would be an endless process (i.e., in each iteration the data is being modified in the source table faster than the replay processes can be completed). In such cases, an undesirable system downtime must be scheduled.
These procedures or functions for reorganizing or moving a database table can have many additional disadvantages. For example, (1) the staging area that is used to track the changes made to data of the source table takes up disk or memory space, (2) since the modifications to the data of the source table and stored in the staging area need to be replayed to the target table, it is required that the reorganization or move of the table to be completed as quickly as possible, and (3) the source table must be locked to switch to the source table with the target table, which can be problematic for source tables which are in frequent or constant use. Additionally, in some cases the source table must be locked during the replay phase, which can cause data inconsistencies and/or delays in the use of the source table.
Accordingly, there is a need for more efficient methods for reorganizing or moving database tables.